import cx_Oracle
import xlwt
from datetime import datetime
from pypinyin import pinyin, lazy_pinyin, Style

dsn = cx_Oracle.makedsn("localhost", 11521, sid="spring")
connection = cx_Oracle.connect(user="bos", password='bosatspring',
                               dsn=dsn,
                               encoding='gb18030',
                               nencoding="UTF-8")

summarywb = xlwt.Workbook()
summaryws = summarywb.add_sheet('Sheet 1')
titles = ["序号", "姓名", "性别", "出生日期", "证件类型", "证件号码", "工作单位", "通讯地址", "献血次数", "总献血量", "联系电话"]
for i, value in enumerate(titles):
    summaryws.write(0, i, value)

with connection.cursor() as cur:
    for idx, info in enumerate(cur.execute("""
    with t as
            (
                select distinct IDENTITYTYPE, IDENTITYID
                from mv_col c
                where c.COLLECTDATE >= date'2020-01-01'
                and c.COLLECTDATE < date'2022-01-01'
            ),
        tt as
            (select c.IDENTITYTYPE,
                    c.IDENTITYID,
                    max(c.serialid) keep (dense_rank first order by c.collectdate desc) serialid,
                    sum(case
                            when c.COLLECTDATE < date'2010-01-01' and c.COLLECTTYPE = 2 then c.PLATELET / 10 * 4
                        when c.COLLECTDATE >= date'2010-01-01' and c.COLLECTTYPE = 2 then c.PLATELET / 10
                            else c.WHOLE/200 end)                                     cnt
            from MV_COL c,
                t
            where c.IDENTITYTYPE = t.IDENTITYTYPE
                and c.IDENTITYID = t.IDENTITYID
                and c.COLLECTDATE < date'2022-01-01'
            group by c.IDENTITYTYPE, c.IDENTITYID
            )
    select
        d.IDENTITYTYPE,
        d.NAME                        姓名,
        decode(d.sex, 1, '男', 0, '女') 性别,
        d.BIRTHDAY                    出生日期,
        it.NAME                       证件类型,
        d.IDENTITYID                  证件号码,
        d.EMPLOYMENTNAME              工作单位,
        d.ADDRESS                     通讯地址,
        tt.cnt                        献血次数,
        tt.cnt * 200                  总献血量,
        (select to_char(wm_concat(dp.phoneno)) from DM_DONATORPHONE dp where dp.DONATORID = d.DONATORID) 联系电话
    from tt,
        donation don,
        dm_donator d,
        d_identitytype it
    where tt.serialid = don.SERIALID
    and don.DONATORID = d.DONATORID
    and d.IDENTITYTYPE = it.ID
    and tt.cnt >= 10
    order by tt.cnt desc, d.name
    """)):
        print(f"processing {idx + 1}: {info[1]}")

        summaryws.write(idx + 1, 0, idx + 1)
        for i, v in enumerate(info):
            if i > 0:
                if type(info[i]) is datetime:
                    summaryws.write(idx + 1, i, info[i].strftime('%Y-%m-%d'))
                else: 
                    summaryws.write(idx + 1, i, v)

        wb = xlwt.Workbook()
        ws = wb.add_sheet('Sheet 1')
        titles = ["申报截止时间", "证件号码", "姓名", "拼音", "性别", "出生日期", "工作单位", "曾获奖项", "获奖时间", 
            "申报奖项", "献血日期", "献血种类", "献血量", "行政区划", "填表人", "填表时间"]
        for i, value in enumerate(titles):
            ws.write(0, i, value)
        datestyle = xlwt.easyxf(num_format_str='YYYY-M-D')
        with connection.cursor() as cur:
            for i, row in enumerate(cur.execute(f"""
            select '2021-12-31'                               申报截止时间,
                c.IDENTITYID                                  证件号码,
                d.NAME                                        姓名,
                decode(d.sex, 1, '男', 0, '女')               性别,
                d.BIRTHDAY                                    出生日期,
                d.EMPLOYMENTNAME                              工作单位,
                ''                                            曾获奖项,
                ''                                            获奖时间,
                ''                                            申报奖项,
                c.COLLECTDATE                                 献血日期,
                decode(c.COLLECTTYPE, 1, '全血', 2, '单采')       献血种类,
                decode(c.COLLECTTYPE, 1, c.WHOLE, c.PLATELET/10) 献血量,
                '广东省珠海市'                                      行政区划,
                '何伟团'                                         填表人,
                '2021-8-1'                                    填表时间
            from mv_col c,
                (select *
                from DM_DONATOR
                where IDENTITYID = '{info[5]}' and IDENTITYTYPE = '{info[0]}' and ROWNUM = 1
                order by UPDATETIME) d
            where c.IDENTITYID = '{info[5]}'
            and c.IDENTITYTYPE = '{info[0]}'
            and c.COLLECTDATE < date'2022-01-01'
            order by c.COLLECTDATE""")):
                namepy = ''.join(lazy_pinyin(row[2], style=Style.FIRST_LETTER)).upper()
                row = list(row)
                row.insert(3, namepy)
                for j, value in enumerate(row): 
                    if type(row[j]) is datetime:
                        ws.write(i + 1, j, row[j].strftime('%Y-%m-%d'))
                    else: 
                        ws.write(i + 1, j, value)
        wb.save('献血记录/{:0>4d}-{}-{}次.xls'.format(idx + 1, info[1], info[8]))
    summarywb.save('汇总表.xls')